/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package br.com.database;

import br.com.models.Graduation;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 *
 * @author Valdigleis
 */
public class DaoGraduation {
    
    private String sql;
    
    private Connection connection;

    /**
     * Método construtor que implementa a classe
     * DaoGraduation, que é responsavel pela comunicação
     * do sistema com a base de dados, para objetos
     * da classe Graduation.
     *  
     */
    public DaoGraduation() {
        this.connection = Derby.getConnection();
    }
    
    /**
     * 
     * Método responsável por grava as informação do
     * objeto graduation na tabela APP.GRADUATION da base
     * de dados.
     * 
     * @param graduation 
     */
    public void save(Graduation graduation){
        try{
            sql = "INSERT INTO SAD.GRADUATION" +
                " (level, situation, time)" + " values (?,?,?)";
            PreparedStatement stmt = connection.prepareStatement(sql);
            stmt.setString(1, graduation.getLevel());
            stmt.setString(2, graduation.getSituation());
            stmt.setInt(3, graduation.getTime());
            stmt.execute();
            stmt.close();
            System.out.println("Graduação salva na base!");
        }catch(SQLException e){
            System.out.println(e.getMessage());
        }
    }
    
    /**
     * Método responsavel por captura o id do ultimo professor na base.
     * @return id
     */
    public int idFinalGraduation(){
        try{
            sql =  "SELECT MAX(id) as id FROM SAD.GRADUATION";
            PreparedStatement stmt = (PreparedStatement) connection.prepareStatement(sql); 
            ResultSet rs = stmt.executeQuery(); 
            rs.next(); 
            int lastId = rs.getInt("id"); 
            rs.close(); 
            stmt.close();
            return lastId; 
        }catch(SQLException e){
            System.out.println(e.getMessage());
            return -1;
        }
    }
   
    
    public Graduation getGaduation(int idGraduation){
        try{
            Graduation graduation = new Graduation();
            sql =  "SELECT * FROM SAD.GRADUATION WHERE id=?";
            PreparedStatement stmt = (PreparedStatement) connection.prepareStatement(sql); 
            stmt.setInt(1, idGraduation);
            ResultSet rs = stmt.executeQuery(); 
            rs.next();
            graduation.setId(rs.getInt("id"));
            graduation.setLevel(rs.getString("level"));
            graduation.setSituation(rs.getString("situation"));
            graduation.setTime(rs.getInt("time"));
            rs.close(); 
            stmt.close();
            return graduation;
        }catch(SQLException e){
            System.out.println(e.getMessage());
            return null;
        }
    }
    
    
    /**
     * Método que atualização as informações da graduação
     * na base de dados.
     * 
     * @param teacher 
     */
    public void update(Graduation graduation){
        try{
            sql = "UPDATE SAD.GRADUATION set level=?, situation=?, time=? WHERE id=?";
            PreparedStatement stmt = connection.prepareStatement(sql);
            stmt.setString(1, graduation.getLevel());
            stmt.setString(2, graduation.getSituation());
            stmt.setInt(3, graduation.getTime());
            stmt.setInt(4, graduation.getId());
            stmt.execute();
            stmt.close();
            System.out.println("Graduação atualizada na base!");
        }catch(SQLException e){
            System.out.println(e.getMessage());
        }
    }
    
    /**
     * Método que encerar a conexão com o banco.
     */
    public void closeConnection(){
        try{
            this.connection.close();
        }catch(SQLException e){
            System.out.println(e.getMessage());
        }
    }
}
